package com.study.jdbc.crud;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.study.jdbc.domain.Item;
import com.study.jdbc.utils.DBUtil;

/**
* @Name: ItemCRUDImpl
* @Description: 商品记录的CRUD操作接口实现类
* @Author: 李超	lc_jack@foxmail.com
* @CreateDate: 2017-2-16 下午2:52:00 
* @Version: V1.0
 */
public class ItemCRUDImpl implements ItemCRUD {

	@Override
	public Item findItemById(Integer id) {
		Connection con = null ;
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		Item item = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			String sql = "select id, name, price, detail, pic, createtime from tb_items where id = ? ;" ;
			//创建sql语句执行对象
			ps = con.prepareStatement(sql) ;
			//执行sql语句，获取结果集对象
			ps.setInt(1, id) ;
			rs = ps.executeQuery() ;
			//返回查询结果
			item = new Item() ;
			if(rs.next()) {
				item.setId(rs.getInt("id")) ;
				item.setName(rs.getString("name")) ;
				item.setPrice(rs.getFloat("price")) ;
				item.setDetail(rs.getString("detail")) ;
				item.setPic(rs.getString("pic")) ;
				item.setCreatetime(rs.getDate("createtime")) ;
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, ps, rs) ;
		}
		return item;
	}

	@Override
	public List<Item> findAllItems() {
		Connection con = null ;
		Statement s = null ; 
		ResultSet rs = null ;
		List<Item> items = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			//创建sql语句执行对象
			s = con.createStatement() ;
			//执行sql语句，获取结果集对象
			String sql = "select id, name, price, detail, pic, createtime from tb_items ; " ;
			rs = s.executeQuery(sql) ;
			//遍历结果集
			items = new ArrayList<Item>() ;
			while(rs.next()) {
				Item item = new Item() ;
				item.setId(rs.getInt("id")) ;
				item.setName(rs.getString("name")) ;
				item.setPrice(rs.getFloat("price")) ;
				item.setDetail(rs.getString("detail")) ;
				item.setPic(rs.getString("pic")) ;
				item.setCreatetime(rs.getDate("createtime")) ;
				items.add(item) ;
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, s, rs) ;
		}
		return items ;
	}

	@Override
	public void insertItem(Item item) {
		Connection con = null ;
		PreparedStatement ps = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			//创建sql语句执行对象
			String sql = "insert into " +
				"tb_items(id, name, price, detail, pic, createtime)" +
				"values(?, ?, ?, ?, ?, ?)" ;
			//预处理
			ps = con.prepareStatement(sql) ;
			ps.setInt(1, item.getId()) ;
			ps.setString(2, item.getName()) ;
			ps.setFloat(3, item.getPrice()) ;
			ps.setString(4, item.getDetail()) ;
			ps.setString(5, item.getPic()) ;
			ps.setDate(6, item.getCreatetime()) ;
			//执行sql语句
			ps.executeUpdate() ;
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, ps, null) ;
		}
	}

	@Override
	public void updateItem(Item item) {
		Connection con = null ;
		PreparedStatement ps = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			String sql = "update tb_items set " +
				" name = ?, " +
				" price = ?, " +
				" detail = ?, " +
				" pic = ?, " +
				" createtime = ? " +
				" where id = ? ; " ;
			//创建sql语句执行对象
			ps = con.prepareStatement(sql) ;
			//预处理
			ps.setString(1, item.getName()) ;
			ps.setFloat(2, item.getPrice()) ;
			ps.setString(3, item.getDetail()) ;
			ps.setString(4, item.getPic()) ;
			ps.setDate(5, item.getCreatetime()) ;
			ps.setInt(6, item.getId()) ;
			//执行sql语句
			ps.executeUpdate() ;
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, ps, null) ;
		}
	}

	@Override
	public void deleteById(Integer id) {
		Connection con = null ;
		PreparedStatement ps = null ;
		try {
			con = DBUtil.getConnection() ;
			String sql = "delete from tb_items where id = ? ; " ;
			ps = con.prepareStatement(sql) ;
			ps.setInt(1, id) ;
			ps.executeUpdate() ;
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			DBUtil.close(con, ps, null) ;
		}
	}

}
